package com.example.nocodesqlapi.service;

import com.alibaba.fastjson.JSON;
import com.example.nocodesqlapi.model.SqlConfig;
import com.example.nocodesqlapi.model.SqlExecuteLog;
import com.example.nocodesqlapi.model.SqlVariable;
import com.example.nocodesqlapi.repository.SqlExecuteLogRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Service
public class SqlExecuteService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    private SqlExecuteLogRepository sqlExecuteLogRepository;

    /**
     * 执行SQL并返回结果
     */
    @Transactional
    public Map<String, Object> executeSql(SqlConfig sqlConfig, Map<String, Object> params) {
        long startTime = System.currentTimeMillis();
        SqlExecuteLog log = new SqlExecuteLog();
        log.setSqlConfigId(sqlConfig.getId());
        log.setApiPath(sqlConfig.getApiPath());
        log.setRequestParams(JSON.toJSONString(params));

        Map<String, Object> result = new HashMap<>();

        try {
            // 解析变量
            List<SqlVariable> variables = JSON.parseArray(sqlConfig.getVariables(), SqlVariable.class);

            // 验证必填参数
            validateRequiredParams(variables, params);

            // 替换SQL中的变量
            String processedSql = processSqlVariables(sqlConfig.getSqlContent(), variables, params);
            log.setExecutedSql(processedSql);

            // 根据SQL类型执行
            String upperSql = processedSql.trim().toUpperCase();
            // 修正：将upperType 替换为 upperSql
            if (upperSql.startsWith("SELECT")) {
                // 查询操作
                List<Map<String, Object>> queryResult = namedParameterJdbcTemplate.query(
                        processedSql,
                        new HashMap<>(params),
                        new ColumnMapRowMapper()
                );
                result.put("success", true);
                result.put("data", queryResult);
                result.put("count", queryResult.size());
                log.setResult("success");
            } else if (upperSql.startsWith("INSERT") || upperSql.startsWith("UPDATE") || upperSql.startsWith("DELETE")) {
                // 增删改操作
                int affectedRows = namedParameterJdbcTemplate.update(processedSql, new HashMap<>(params));
                result.put("success", true);
                result.put("affectedRows", affectedRows);
                log.setResult("success");
            } else {
                throw new RuntimeException("不支持的SQL操作类型");
            }
        } catch (Exception e) {
            result.put("success", false);
            result.put("error", e.getMessage());
            log.setResult("fail");
            log.setErrorMsg(e.getMessage());
        } finally {
            // 记录执行时间
            log.setExecuteTime(System.currentTimeMillis() - startTime);
            sqlExecuteLogRepository.save(log);
        }

        return result;
    }

    /**
     * 验证必填参数
     */
    private void validateRequiredParams(List<SqlVariable> variables, Map<String, Object> params) {
        if (variables == null || variables.isEmpty()) {
            return;
        }

        for (SqlVariable variable : variables) {
            if (variable.isRequired() && (!params.containsKey(variable.getName()) ||
                    params.get(variable.getName()) == null ||
                    StringUtils.isEmpty(params.get(variable.getName())))) {
                throw new RuntimeException("缺少必填参数: " + variable.getName());
            }
        }
    }

    /**
     * 处理SQL中的变量
     */
    private String processSqlVariables(String sql, List<SqlVariable> variables, Map<String, Object> params) {
        if (variables == null || variables.isEmpty()) {
            return sql;
        }

        String processedSql = sql;
        for (SqlVariable variable : variables) {
            String varName = variable.getName();
            // 设置默认值
            if (!params.containsKey(varName) || params.get(varName) == null) {
                if (variable.getDefaultValue() != null) {
                    params.put(varName, variable.getDefaultValue());
                } else {
                    continue;
                }
            }

            // 处理特殊类型
            if ("Date".equals(variable.getType())) {
                // 可以在这里添加日期类型的特殊处理
            }
        }

        return processedSql;
    }

    /**
     * 自定义RowMapper，将列名转换为驼峰命名
     */
    private static class ColumnMapRowMapper implements RowMapper<Map<String, Object>> {
        @Override
        public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
            Map<String, Object> map = new HashMap<>();
            int columnCount = rs.getMetaData().getColumnCount();

            for (int i = 1; i <= columnCount; i++) {
                String columnName = rs.getMetaData().getColumnName(i);
                Object value = rs.getObject(i);

                // 转换为驼峰命名
                String camelCaseName = underlineToCamel(columnName);
                map.put(camelCaseName, value);
            }

            return map;
        }

        // 下划线转驼峰
        private String underlineToCamel(String name) {
            if (name == null || name.isEmpty()) {
                return name;
            }

            StringBuilder result = new StringBuilder();
            boolean nextUpperCase = false;

            for (int i = 0; i < name.length(); i++) {
                char c = name.charAt(i);
                if (c == '_') {
                    nextUpperCase = true;
                } else {
                    if (nextUpperCase) {
                        result.append(Character.toUpperCase(c));
                        nextUpperCase = false;
                    } else {
                        result.append(Character.toLowerCase(c));
                    }
                }
            }

            return result.toString();
        }
    }
}

